<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Excel 数据处理</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f4f4f4;
            margin: 0;
            padding: 20px;
            display: flex;
            flex-direction: column;
            min-height: 100vh;
        }

        h1,
        h2 {
            color: #333;
        }

        input,
        select {
            padding: 10px;
            margin: 5px;
            border: 1px solid #ccc;
            border-radius: 5px;
            font-size: 16px;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
            background-color: #fff;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
        }

        th,
        td {
            padding: 12px 15px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }

        th {
            background-color: #007bff;
            color: white;
            font-weight: bold;
        }

        tr:nth-child(even) {
            background-color: #f2f2f2;
        }

        tr:hover {
            background-color: #ddd;
        }

        tfoot {
            font-weight: bold;
        }

        footer {
            margin-top: auto;
            text-align: center;
            color: #777;
            font-size: 14px;
            padding: 10px 0;
            /* background-color: #fff; */
            /* box-shadow: 0 -2px 10px rgba(0, 0, 0, 0.1); */
        }

        .form-group {
            display: flex;
            align-items: center;
            margin-right: 20px;
        }
    </style>
</head>

<body>

    <h1>数学周测成绩分析-需要有班级和成绩的表头名称</h1>
    <!-- 上传Excel文件 -->
    <div class="form-group">
        <input type="file" id="file-input" />
    </div>
    <div class="form-group">
        <label for="start-class">起始班级：</label>
        <input type="number" id="start-class" placeholder="请输入起始班级序号" />
    </div>

    <div class="form-group">
        <label for="end-class">结束班级：</label>
        <input type="number" id="end-class" placeholder="请输入结束班级序号" />
    </div>

    <div class="form-group">
        <label for="score-threshold">指定分数(>=)：</label>
        <input type="number" id="score-threshold" value="0" placeholder="默认为0" />
    </div>

    <div class="form-group">
        <label for="sort-by">排序方式：</label>
        <select id="sort-by">
            <option value="totalPoints">积分</option>
            <option value="avgScore">平均分</option>
            <option value="maxScore">最高分</option>
            <option value="thresholdCount">指定分数</option>
            <option value="excellentRate">优秀率</option>
            <option value="passRate">及格率</option>
        </select>
    </div>
    <!-- 显示处理结果 -->
    <!-- 显示处理结果 -->
    <!-- 显示处理结果 -->
    <table id="result-table" border="1" style="margin-top: 20px;">
        <thead>
            <tr>
                <th>班级</th>
                <th>平均分</th>
                <th>积分</th>
                <th>排名</th>
                <th>最高分</th>
                <th>最低分</th>
                <th>超过指定分数人数</th>
                <th>优秀人数</th>
                <th>优秀率</th>
                <th>及格人数</th>
                <th>及格率</th>
                <th>120</th>
                <th>[114,120)</th>
                <th>[108,114)</th>
                <th>[102,108)</th>
                <th>[96,102)</th>
                <th>[84,96)</th>
                <th>[72,84)</th>
                <th>[60,72)</th>
                <th>[48,60)</th>
                <th>[0,48)</th>
            </tr>
        </thead>
        <tbody>
        </tbody>
    </table>

    <footer>
        <p>数学：zc-2024</p>
    </footer>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.2/xlsx.full.min.js"></script>

    <script>
        document.getElementById('file-input').addEventListener('change', handleFile, false);
        document.getElementById('start-class').addEventListener('change', reprocessData, false);
        document.getElementById('end-class').addEventListener('change', reprocessData, false);
        document.getElementById('score-threshold').addEventListener('change', reprocessData, false);
        document.getElementById('sort-by').addEventListener('change', reprocessData, false);

        let currentRows = []; // 存储当前读取的数据行，以便于后续使用

        function handleFile(event) {
            const file = event.target.files[0];
            if (!file) return;

            const reader = new FileReader();
            reader.onload = function (e) {
                const data = new Uint8Array(e.target.result);
                const workbook = XLSX.read(data, { type: 'array' });

                // 假设文件只有一个工作表
                const sheet = workbook.Sheets[workbook.SheetNames[0]];

                // 将工作表转换成 JSON 格式
                currentRows = XLSX.utils.sheet_to_json(sheet, { header: 1 }); // 存储数据行

                // 首次加载时处理数据
                reprocessData();
            };
            reader.readAsArrayBuffer(file);
        }

        function reprocessData() {
            // 获取输入的班级范围
            const startClass = parseInt(document.getElementById('start-class').value);
            const endClass = parseInt(document.getElementById('end-class').value);
            const scoreThreshold = parseInt(document.getElementById('score-threshold').value);
            const sortBy = document.getElementById('sort-by').value;

            // 使用存储的数据行处理数据
            processData(currentRows, startClass, endClass, scoreThreshold, sortBy);
        }

        function processData(rows, startClass, endClass, scoreThreshold, sortBy) {
            // 提取表头
            const header = rows[0];
            // const classIndex = 0;  // 班级列在第一列 (0索引)
            const classIndex = header.indexOf("班级");
            const scoreIndex = header.indexOf("成绩");

            // 组织班级数据
            const classData = {};

            // 遍历每一行数据，计算班级平均分、最高分、最低分和>=XX人数
            for (let i = 1; i < rows.length; i++) {
                const row = rows[i];
                const className = row[classIndex];
                const score = row[scoreIndex];

                // 如果班级名称为 undefined 或者成绩为"缺考"或者不是有效数字，或者班级不在指定范围内，则跳过该行
                if (className === undefined || (isNaN(score) || score === "缺考") || className < startClass || className > endClass) {
                    continue;
                }

                // 确保成绩为数字
                const numericScore = parseFloat(score);
                if (!isNaN(numericScore)) {
                    if (!classData[className]) {
                        classData[className] = {
                            totalScore: 0,
                            count: 0,
                            nonZeroCount: 0, // 新增：仅用于统计非零分数的数量，用于计算平均分
                            maxScore: -Infinity,
                            minScore: Infinity,
                            thresholdCount: 0,
                            excellentCount: 0,
                            passCount: 0,
                            totalPoints: 0,
                            segmentCounts: {
                                '120': 0,
                                '[114,120)': 0,
                                '[108,114)': 0,
                                '[102,108)': 0,
                                '[96,102)': 0,
                                '[84,96)': 0,
                                '[72,84)': 0,
                                '[60,72)': 0,
                                '[48,60)': 0,
                                '[0,48)': 0
                            }
                        };
                    }
                    classData[className].totalScore += numericScore;
                    classData[className].count += 1;
                    classData[className].maxScore = Math.max(classData[className].maxScore, numericScore);
                    classData[className].minScore = Math.min(classData[className].minScore, numericScore);
                    if (numericScore != 0) { // 忽略0分，仅对非0分进行额外的计数
                        classData[className].nonZeroCount += 1;
                    }
                    if (numericScore >= scoreThreshold) {
                        classData[className].thresholdCount += 1;
                    }

                    if (numericScore >= 96) {
                        classData[className].excellentCount += 1;
                    }

                    if (numericScore >= 72) {
                        classData[className].passCount += 1;
                    }

                    // 计算积分
                    if (numericScore == 120) {
                        classData[className].totalPoints += 15;
                        classData[className].segmentCounts['120'] += 1;
                    } else if (numericScore >= 114) {
                        classData[className].totalPoints += 14;
                        classData[className].segmentCounts['[114,120)'] += 1;
                    } else if (numericScore >= 108) {
                        classData[className].totalPoints += 12;
                        classData[className].segmentCounts['[108,114)'] += 1;
                    } else if (numericScore >= 102) {
                        classData[className].totalPoints += 10;
                        classData[className].segmentCounts['[102,108)'] += 1;
                    } else if (numericScore >= 96) {
                        classData[className].totalPoints += 8;
                        classData[className].segmentCounts['[96,102)'] += 1;
                    } else if (numericScore >= 84) {
                        classData[className].totalPoints += 6;
                        classData[className].segmentCounts['[84,96)'] += 1;
                    } else if (numericScore >= 72) {
                        classData[className].totalPoints += 4;
                        classData[className].segmentCounts['[72,84)'] += 1;
                    } else if (numericScore >= 60) {
                        classData[className].totalPoints += 2;
                        classData[className].segmentCounts['[60,72)'] += 1;
                    } else if (numericScore >= 48) {
                        classData[className].totalPoints += 1;
                        classData[className].segmentCounts['[48,60)'] += 1;
                    } else if (numericScore >= 0) {
                        classData[className].totalPoints += 0;
                        classData[className].segmentCounts['[0,48)'] += 1;
                    }
                }
            }

            // 计算班级的平均分、优秀率和及格率部分也需要相应调整
            const classAvg = [];
            for (const className in classData) {
                // 使用nonZeroCount计算平均分，避免除以0的情况
                const avgScore = classData[className].nonZeroCount > 0 ? classData[className].totalScore / classData[className].nonZeroCount : 0;
                const excellentRate = (classData[className].excellentCount / classData[className].count) * 100;
                const passRate = (classData[className].passCount / classData[className].count) * 100;
                classAvg.push({
                    className,
                    avgScore,
                    maxScore: classData[className].maxScore,
                    minScore: classData[className].minScore,
                    thresholdCount: classData[className].thresholdCount,
                    excellentCount: classData[className].excellentCount,
                    excellentRate,
                    passCount: classData[className].passCount,
                    passRate,
                    totalPoints: classData[className].totalPoints,
                    segmentCounts: classData[className].segmentCounts
                });
            }

            // 按照用户选择的方式排序
            classAvg.sort((a, b) => {
                switch (sortBy) {
                    case 'avgScore':
                        return b.avgScore - a.avgScore;
                    case 'maxScore':
                        return b.maxScore - a.maxScore;
                    case 'thresholdCount':
                        return b.thresholdCount - a.thresholdCount;
                    case 'excellentRate':
                        return b.excellentRate - a.excellentRate;
                    case 'passRate':
                        return b.passRate - a.passRate;
                    case 'totalPoints':
                        return b.totalPoints - a.totalPoints;
                    default:
                        return b.avgScore - a.avgScore;
                }
            });

            // 给每个班级排名
            classAvg.forEach((item, index) => {
                item.rank = index + 1;
            });

            // 更新表格
            updateTable(classAvg);
        }

        function updateTable(classAvg) {
            const tbody = document.querySelector("#result-table tbody");
            tbody.innerHTML = '';

            classAvg.forEach(item => {
                const tr = document.createElement("tr");
                const tdClass = document.createElement("td");
                const tdAvgScore = document.createElement("td");
                const tdTotalPoints = document.createElement("td");
                const tdRank = document.createElement("td");
                const tdMaxScore = document.createElement("td");
                const tdMinScore = document.createElement("td");
                const tdThresholdCount = document.createElement("td");
                const tdExcellentCount = document.createElement("td");
                const tdExcellentRate = document.createElement("td");
                const tdPassCount = document.createElement("td");
                const tdPassRate = document.createElement("td");


                tdClass.textContent = item.className;
                tdAvgScore.textContent = item.avgScore.toFixed(2);  // 保留两位小数
                tdMaxScore.textContent = item.maxScore;
                tdMinScore.textContent = item.minScore;
                tdThresholdCount.textContent = item.thresholdCount;
                tdExcellentCount.textContent = item.excellentCount;
                tdExcellentRate.textContent = item.excellentRate.toFixed(2) + '%';  // 保留两位小数
                tdPassCount.textContent = item.passCount;
                tdPassRate.textContent = item.passRate.toFixed(2) + '%';  // 保留两位小数
                tdTotalPoints.textContent = item.totalPoints;
                tdRank.textContent = item.rank;

                // 添加分数段人数列
                const tdSegment120 = document.createElement("td");
                const tdSegment114_120 = document.createElement("td");
                const tdSegment108_114 = document.createElement("td");
                const tdSegment102_108 = document.createElement("td");
                const tdSegment96_102 = document.createElement("td");
                const tdSegment84_96 = document.createElement("td");
                const tdSegment72_84 = document.createElement("td");
                const tdSegment60_72 = document.createElement("td");
                const tdSegment48_60 = document.createElement("td");
                const tdSegment0_48 = document.createElement("td");


                tdSegment120.textContent = item.segmentCounts['120'];
                tdSegment114_120.textContent = item.segmentCounts['[114,120)'];
                tdSegment108_114.textContent = item.segmentCounts['[108,114)'];
                tdSegment102_108.textContent = item.segmentCounts['[102,108)'];
                tdSegment96_102.textContent = item.segmentCounts['[96,102)'];
                tdSegment84_96.textContent = item.segmentCounts['[84,96)'];
                tdSegment72_84.textContent = item.segmentCounts['[72,84)'];
                tdSegment60_72.textContent = item.segmentCounts['[60,72)'];
                tdSegment48_60.textContent = item.segmentCounts['[48,60)'];
                tdSegment0_48.textContent = item.segmentCounts['[0,48)'];


                tr.appendChild(tdClass);
                tr.appendChild(tdAvgScore);
                tr.appendChild(tdTotalPoints);
                tr.appendChild(tdRank);
                tr.appendChild(tdMaxScore);
                tr.appendChild(tdMinScore);
                tr.appendChild(tdThresholdCount);
                tr.appendChild(tdExcellentCount);
                tr.appendChild(tdExcellentRate);
                tr.appendChild(tdPassCount);
                tr.appendChild(tdPassRate);
                tr.appendChild(tdSegment120);
                tr.appendChild(tdSegment114_120);
                tr.appendChild(tdSegment108_114);
                tr.appendChild(tdSegment102_108);
                tr.appendChild(tdSegment96_102);
                tr.appendChild(tdSegment84_96);
                tr.appendChild(tdSegment72_84);
                tr.appendChild(tdSegment60_72);
                tr.appendChild(tdSegment48_60);
                tr.appendChild(tdSegment0_48);


                tbody.appendChild(tr);
            });
        }
    </script>

</body>

</html>